﻿CREATE PROCEDURE [dbo].[proc_Finance_RunningAccount_GetList_AccountsTypeId]
	(
		@CompanyId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@PaymentTypeId int,
		@BankId int,
		@Renling int,
		@Stext nvarchar(50),
		@AccountsTypeId int,
		@StartIndex int,
		@EndIndex int,
		@checkxj bit,
		@rblbank INT,
		@Agent VARCHAR(20),
		@str VARCHAR (200)
	)
AS
BEGIN
	
	DECLARE @TempStr VARCHAR(500),@StrSql NVARCHAR(4000)
SET @TempStr=''
if @CompanyId<>0
begin
	SET @TempStr=@TempStr+' R.CompanyId='+CONVERT(VARCHAR, @CompanyId)+' and '
end

if @AccountsTypeId<>0
begin
	SET @TempStr=@TempStr+' R.AccountsTypeId='+ CONVERT(VARCHAR, @AccountsTypeId)+' and '
end

if @Renling<>0
begin
	SET @TempStr=@TempStr+' R.Renling='+ CONVERT(VARCHAR, @Renling)+' and '
end

IF @PaymentTypeId=1 --现金
BEGIN   --查现金时，还需要查询电汇中需要算在现金中的电汇账号
	--SET @TempStr='(R.PaymentTypeId=1 
	--                  or r.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid='+CONVERT(VARCHAR, @CompanyId)+')) and '
    IF @checkxj=0
    BEGIN
    	SET @TempStr=@TempStr+'R.PaymentTypeId=1  and '
    END
    ELSE 
    	BEGIN
    		SET @TempStr=@TempStr+'(R.PaymentTypeId=1 
	                  or r.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid='+CONVERT(VARCHAR, @CompanyId)+')) and '
    	END
END
ELSE IF @PaymentTypeId=2 --银行
BEGIN
	IF  @BankId=0 --电汇账号选择为全部时，查询所有算在银行中的电汇金额
	BEGIN
		SET @TempStr=@TempStr+'R.PaymentTypeId=2  and r.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+' and Companyid='+CONVERT(VARCHAR, @CompanyId)+') and '
	END
	ELSE
		BEGIN--选择了电汇账号时，查询该账号下的金额
			SET @TempStr=@TempStr+'R.PaymentTypeId=2  and  r.BankId = (select id from Finance_Bank where id='+CONVERT(VARCHAR, @BankId)+' and  PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+') and '
		END
END
ELSE IF @PaymentTypeId=3 --支票
BEGIN
	SET @TempStr=@TempStr+'(R.PaymentTypeId=3 ) and '
END
ELSE
	BEGIN
		set @TempStr=@TempStr+' R.PaymentTypeId in(1,2,3)  and '
	END
IF @Agent<>''
BEGIN
	SET @TempStr=@TempStr+' UserName='''+@Agent+''' and  '
END

if @Stext <>''
begin 
	SET @TempStr=@TempStr+' R.Summary like ''%'+ @Stext +'%'' and '
end
	
	SET @StrSql='
	;WITH list As(					
Select ROW_NUMBER() OVER (ORDER BY R.Renling asc,createdate desc)AS Row,
		R.Id,
		R.RId,
		R.CreateDate,
		R.TradeDate,
		R.Summary,
		R.AccountsTypeId,
		R.AccountsTypeName,
		ISNULL(R.ParentId,0) As ParentId,
		R.ParentName,
		ISNULL(R.SubjectId,0) As SubjectId,
		R.SubjectName,
		R.PaymentTypeId,
		R.PaymentTypeName,
		R.BankId,
		R.BankName,
		R.Income,
		R.Outlay,
		R.Agent,
		R.AgentName,
		R.UserName,
		R.EName,
		R.BillNumber,
		R.OrderId,
		R.Renling,	
		R.CompanyId,
		(Select CompanyName From Company
		Where Id=R.CompanyId) As CompanyName

	From Finance_RunningAccount R
	Where 	 
	 '+@TempStr+'  R.TradeDate between '''+@StartDate+''' and '''+@EndDate+'''
	   '+@str+'	 )

	Select *,  (Select Count(Id) From list
	) as cid,(Select IsNULL(Sum(Case '+ CONVERT(VARCHAR, @AccountsTypeId)+'  When 1 Then Income Else Outlay End),0) From list
	) as tsum From list Where Row between '+  CONVERT(VARCHAR, @StartIndex) +' and '+ CONVERT(VARCHAR, @EndIndex) +'	   
	   '
	   --PRINT(@StrSql)
	   EXEC(@StrSql)
	   
end
	RETURN
